package com.kusej.dbdocexport.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardOpenOption;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.Date;
import java.util.HashSet;

/**
 * Excel工具
 *
 * @author: kaixuan
 * @date: 2023/6/15 17:57
 **/
public class ExcelUtils {


    private ExcelUtils(){

    }

    /**
     * getCellWithType时支持转换的类型
     */
    private static final HashSet<Class<?>> SUPPORT_CLASS_SET;

    /**
     * 列宽最大为255英寸
     */
    private static final int EXCEL_MAX_ACCURATE_WIDTH = 255;
    /**
     * 超过十位会被科学计数法
     */
    private static final long SCIENTIFIC_NUM = 10000000000L;
    /**
     * 数据量大于10000建议使用SXSSFWorkbook
     */
    private static final int DATA_WORKBOOK_SIZE = 1_0000;

    /**
     * 不打算加入太多
     * 例如你需要Float 那么Double.floatValue()
     */
    static {
        SUPPORT_CLASS_SET = new HashSet<>(16);
        SUPPORT_CLASS_SET.add(Double.class);
        SUPPORT_CLASS_SET.add(Integer.class);
        SUPPORT_CLASS_SET.add(Long.class);
        SUPPORT_CLASS_SET.add(BigDecimal.class);
        SUPPORT_CLASS_SET.add(String.class);
        SUPPORT_CLASS_SET.add(LocalDate.class);
        SUPPORT_CLASS_SET.add(LocalTime.class);
        SUPPORT_CLASS_SET.add(LocalDateTime.class);
    }

    /**
     * 常用的数据格式，一般是Date单元格需要用到
     */
    public enum DataFormatEnum{
        /**
         * Excel默认日期的格式
         */
        DATE_DEFAULT("yyyy/m/d"),
        /**
         * 常用标准日期
         */
        DATE_USUAL("yyyy-m-d"),
        /**
         * 常用标准日期时间
         */
        DATE_TIME_USUAL("yyyy-m-d h:mm:ss"),
        /**
         * 中文日期
         */
        DATE_CHINESE("yyyy年m月d日"),
        /**
         * 中文日期时间
         */
        DATE_TIME_CHINESE("yyyy年m月d日 h时mm分ss秒"),
        /**
         * 两位小数
         */
        NUM_TWO_POINTS("#.##"),
        /**
         * 两位小数强制0占位
         */
        NUM_TWO_POINTS_ZERO_FILL("0.00");

        /**
         * 格式
         */
        private final String formatStr;

        DataFormatEnum(String formatStr) {
            this.formatStr = formatStr;
        }

        public String getFormatStr() {
            return formatStr;
        }
    }

    /**
     *
     * @param inputStream
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbookFromFile(InputStream inputStream) throws Exception {
        if (inputStream == null){
            throw new Exception("Excel文件不存在");
        }
        return WorkbookFactory.create(inputStream);
    }
    /**
     * 从File中获取workbook
     * 不论是xls和xlsx都可以
     * @param excelFile
     * @return 异常会返回null 这个方法不会抛异常
     */
    public static Workbook getWorkbookFromFile(File excelFile){
        Workbook workbook;
        if (excelFile == null || excelFile.length() == 0){
            return null;
        }
        if (!excelFile.exists()){
            return null;
        }
        if (!excelFile.canRead()){
            return null;
        }
        try {
            workbook = WorkbookFactory.create(excelFile);
        } catch (Exception e) {
            return null;
        }
        return workbook;
    }
    /**
     * 从inputStream中获取workbook
     * 不论是xls和xlsx都可以
     * @param inputStream
     * @return 异常会返回null 这个方法不会抛异常
     */
    public static Workbook getWorkbookFromStream(InputStream inputStream){
        Workbook workbook;
        if (inputStream == null){
            throw new RuntimeException("输入流为空");
        }
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return workbook;
    }

    /**
     * 获取一个workbook
     * @return xlsx workbook
     */
    public static Workbook newExcel(){
        return new XSSFWorkbook();
    }
    /**
     * 获取一个workbook
     * @param dataSize 超过限制使用SXSSFWorkbook
     * @return xlsx workbook
     */
    public static Workbook newExcel(int dataSize){
        if (dataSize > DATA_WORKBOOK_SIZE){
            SXSSFWorkbook workbook = new SXSSFWorkbook();
            workbook.setCompressTempFiles(true);
            return workbook;
        }else {
            return new XSSFWorkbook();
        }
    }

    /**
     * 获取一个通用的内容样式 不要循环多次调用此方法！避免创建过多style
     * @param workbook HSSFWorkbook
     * @return HSSFCellStyle
     */
    public static CellStyle getGeneralContentStyle(Workbook workbook){
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

        Font font = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        return style;
    }

    /**
     * 获取一个带数据格式的DataFormat 例如日期格式
     * <br /> 不要循环多次调用此方法！避免创建过多DataFormat！
     * @param workbook
     * @param dataFormatStr
     * @return
     */
    public static CellStyle getFormatContentStyle(Workbook workbook,String dataFormatStr){
        CellStyle style = getGeneralContentStyle(workbook);
        DataFormat dataFormat = workbook.createDataFormat();
        style.setDataFormat(dataFormat.getFormat(dataFormatStr));
        return style;
    }
    /**
     * 获取一个带数据格式的DataFormat 例如日期格式
     * <br /> 不要循环多次调用此方法！避免创建过多DataFormat！
     * @param workbook
     * @param formatEnum
     * @return
     */
    public static CellStyle getFormatContentStyle(Workbook workbook,DataFormatEnum formatEnum){
        CellStyle style = getGeneralContentStyle(workbook);
        DataFormat dataFormat = workbook.createDataFormat();
        style.setDataFormat(dataFormat.getFormat(formatEnum.getFormatStr()));
        return style;
    }

    /**
     * 获取表头样式 不要循环多次调用此方法！避免创建过多style
     * @param workbook
     * @return
     */
    public static CellStyle getTableHeaderStyle(Workbook workbook){
        CellStyle style = getGeneralContentStyle(workbook);
        style.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_NORMAL);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        style.setFont(font);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        return style;
    }

    /**
     * 添加一个合并单元格
     * @param sheet
     * @param beginRow 开始行
     * @param endRow 结束行
     * @param beginColumn 开始列
     * @param endColumn 结束列
     * @return 合并单元格 可以继续操作
     */
    public static CellRangeAddress cellRangeAdd(Sheet sheet,int beginRow,int endRow,
                                                int beginColumn,int endColumn){
        CellRangeAddress cellRangeAddress = new CellRangeAddress(beginRow, endRow, beginColumn, endColumn);
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.isInRange(cellRangeAddress.getFirstRow(), cellRangeAddress.getFirstColumn())) {
                // 发现当前要合并的单元格已经属于某个已经存在的合并单元格
                return mergedRegion;
            }
        }
        sheet.addMergedRegion(cellRangeAddress);
        RegionUtil.setBorderLeft(BorderStyle.THIN,cellRangeAddress,sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN,cellRangeAddress,sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN,cellRangeAddress,sheet);
        return cellRangeAddress;
    }
    /**
     * 设置多行样式
     * @param sheet
     * @param rowNum
     * @param startColumnNum 起始列
     * @param endColumnNum 结束列 null时为(getLastCellNum-1)
     * @param style
     */
    public static void setCellStyleRow(Sheet sheet,int rowNum,int startColumnNum,Integer endColumnNum,CellStyle style){
        if (style == null){
            return;
        }
        Row row = sheet.getRow(rowNum);
        if (row == null){
            row = sheet.createRow(rowNum);
        }
        if (endColumnNum == null){
            endColumnNum = row.getLastCellNum()-1;
        }
        for (int i = startColumnNum; i <= endColumnNum; i++) {
            Cell cell = row.getCell(i);
            if (cell == null){
                cell = row.createCell(i);
            }
            cell.setCellStyle(style);
        }
    }

    /**
     * 设置多列样式
     * @param sheet
     * @param startRowNum 起始行
     * @param endRowNum 结束行 null是为getLastRowNum
     * @param columnNum
     * @param style
     */
    public static void setCellStyleColumn(Sheet sheet,int startRowNum,Integer endRowNum,int columnNum,CellStyle style){
        if (style == null){
            return;
        }
        if (endRowNum == null){
            endRowNum = sheet.getLastRowNum();
        }
        for (int i = startRowNum; i <= endRowNum; i++) {
            Row row = sheet.getRow(i);
            if (row == null){
                row = sheet.createRow(i);
            }
            Cell cell = row.getCell(columnNum);
            if (cell == null){
                cell = row.createCell(columnNum);
            }
            cell.setCellStyle(style);
        }
    }


    /**
     * 设置某个单元格的样式
     * @param sheet
     * @param rowNum
     * @param columnNum
     * @param style
     */
    public static void setCellStyle(Sheet sheet,int rowNum,int columnNum,CellStyle style){
        if (style == null){
            return;
        }
        Row row = sheet.getRow(rowNum);
        if (row == null){
            row = sheet.createRow(rowNum);
        }
        Cell cell = row.getCell(columnNum);
        if (cell == null){
            cell = row.createCell(columnNum);
        }
        cell.setCellStyle(style);
    }


    /**
     * 获取单元格的值
     * @param sheet
     * @param rowNum
     * @param columnNum
     * @return
     */
    public static String getCellStringValue(Sheet sheet,int rowNum,int columnNum){
        Row row = sheet.getRow(rowNum);
        if (row == null){
            return "";
        }
        return getCellStringValue(row.getCell(columnNum));
    }
    /**
     * 获取excel文本类型单元格的值
     * @param cell 注意！这个方法不能获取日期类型的单元格，日期会被转换为一个数字
     * @return String类型的值
     */
    public static String getCellStringValue(Cell cell) {
        String tempReturnString = "";
        if (cell == null) {
            tempReturnString = "";
        } else if(cell.getCellType() == CellType.ERROR) {
            tempReturnString = cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            cell.setCellType(CellType.STRING);
            tempReturnString = cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.STRING) {
            tempReturnString = cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.FORMULA) {
            try {
                tempReturnString = cell.getNumericCellValue() + "";
            } catch (IllegalStateException e) {
                tempReturnString = String.valueOf(cell.getRichStringCellValue());
            }
        }
        return tempReturnString.trim();
    }

    /**
     * 根据类型读取Excel单元格的值
     * @see ExcelUtils#SUPPORT_CLASS_SET
     * @param sheet
     * @param rowNum
     * @param columnNum
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> T getCellValueWithType(Sheet sheet,int rowNum,int columnNum,
                                             Class<T> clazz) {
        Row row = sheet.getRow(rowNum);
        if (row == null){
            return null;
        }
        try {
            return getCellValueWithType(row.getCell(columnNum),clazz);
        } catch (RuntimeException e) {
            throw new RuntimeException(combinePositionMsg(rowNum,columnNum,e.getMessage()));
        }
    }

    /**
     * 根据类型读取Excel单元格的值
     * <br />原生的poi用起来比较麻烦 比如cellType是Numeric的试时候 你却不能getCellValue(String)
     * <br />因此 我写了一堆if来处理各种情况 不需要再一直返回String了 而且日期类型的单元格不能用String来处理
     * @see ExcelUtils#SUPPORT_CLASS_SET
     * @param cell
     * @param clazz
     * @param <T>
     * @return 这里处理了，空白单元格取数字类型会返回null，poi会返回0.0D
     */
    @SuppressWarnings("unchecked")
    public static <T> T getCellValueWithType(Cell cell,Class<T> clazz){
        if (cell == null){
            return null;
        }

        if (!SUPPORT_CLASS_SET.contains(clazz)){
            throw new RuntimeException("getCellValueWithType 不支持的转换类型 " + clazz.toString());
        }
        CellType cellType = cell.getCellType();

        //String类型 null转空串转换处理
        if (String.class.equals(clazz)){
            String stringCellValue;
            if (cellType == CellType.NUMERIC) {
                cell.setCellType(CellType.STRING);
                stringCellValue = cell.getStringCellValue();
            }else if (cellType == CellType.FORMULA) {
                try {
                    stringCellValue = cell.getNumericCellValue() + "";
                } catch (IllegalStateException e) {
                    stringCellValue = String.valueOf(cell.getRichStringCellValue());
                }
            }else {
                stringCellValue = cell.getStringCellValue();
            }
            if (stringCellValue == null){
                stringCellValue = "";
            }
            return (T)stringCellValue;
        }

        //日期类型
        try {
            if(LocalDate.class.equals(clazz)){
                LocalDate localDate;
                if (cellType == CellType.STRING){
                    String stringCellValue = cell.getStringCellValue();
                    if (stringCellValue == null){
                        return null;
                    }
                    localDate = DateTimeUtils.parse2LocalDate(stringCellValue.trim());
                }else {
                    double numericCellValue = cell.getNumericCellValue();
                    if (numericCellValue == 0){
                        return null;
                    }
                    //如果大于100万天 说明输入的是yyyyMMdd这种格式的日期类型
                    if (numericCellValue > 3000_0000){
                        throw new RuntimeException("日期超过3000年 " + numericCellValue);
                    } else if (numericCellValue > 100_0000){
                        BigDecimal decimal = new BigDecimal(numericCellValue);
                        String s = Integer.toString(decimal.intValue());
                        localDate = DateTimeUtils.parse2LocalDate(s);
                    }else {
                        LocalDateTime dateCellValue = cell.getLocalDateTimeCellValue();
                        if (dateCellValue == null){
                            return null;
                        }
                        localDate = dateCellValue.toLocalDate();
                    }
                }
                return (T)localDate;
            } else if(LocalTime.class.equals(clazz)){
                LocalTime localTime;
                if (cellType == CellType.STRING){
                    String stringCellValue = cell.getStringCellValue();
                    if (stringCellValue == null){
                        return null;
                    }
                    localTime = DateTimeUtils.parse2LocalTime(stringCellValue.trim());
                }else {
                    LocalDateTime dateCellValue = cell.getLocalDateTimeCellValue();
                    if (dateCellValue == null){
                        return null;
                    }
                    localTime = dateCellValue.toLocalTime();
                }
                return (T)localTime;
            } else if (LocalDateTime.class.equals(clazz)){
                LocalDateTime localDateTime;
                if (cellType == CellType.STRING){
                    String stringCellValue = cell.getStringCellValue();
                    if (stringCellValue == null){
                        return null;
                    }
                    localDateTime = DateTimeUtils.parse2LocalDateTime(stringCellValue.trim());
                }else {
                    double numericCellValue = cell.getNumericCellValue();
                    if (numericCellValue == 0){
                        return null;
                    }
                    //如果大于100万天 说明输入的是yyyyMMdd这种格式的日期类型
                    if (numericCellValue > 100_0000){
                        BigDecimal decimal = new BigDecimal(numericCellValue);
                        String s = Integer.toString(decimal.intValue());
                        localDateTime = DateTimeUtils.parse2LocalDateTime(s);
                    }else {
                        LocalDateTime dateCellValue = cell.getLocalDateTimeCellValue();
                        if (dateCellValue == null){
                            return null;
                        }
                        localDateTime = dateCellValue;
                    }
                }
                return (T)localDateTime;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("getCellValueWithType 日期转换失败 " + e);
        }

        try {
            //数字类型 有点特殊 为了保证高可用采用第一种办法
            //①实际填写 单元格-数字/常规 81.01
            //doubleCellValue getNumericCellValue 81.01 --准
            //stringCellValue getStringCellValue 81.010000000005 --不准！
            //实际填写 单元格-数字/常规 [空白]
            //doubleCellValue getNumericCellValue 0.0 --不准！
            //stringCellValue getStringCellValue null或者空串 --辅助判断
            //②实际填写 单元格-文本 81.01
            //doubleCellValue getNumericCellValue报错类型不匹配 --不可用！
            //stringCellValue getStringCellValue 81.01 --准
            //实际填写 单元格-文本 [空白]
            //doubleCellValue getNumericCellValue报错类型不匹配 --不可用！
            //stringCellValue getStringCellValue null或者空串 --准
            Double doubleCellValue;
            String stringCellValue;
            if (cellType == CellType.STRING){
                stringCellValue = cell.getStringCellValue();
                if (stringCellValue == null || stringCellValue.isEmpty() || stringCellValue.trim().isEmpty()){
                    return null;
                }
                doubleCellValue = Double.parseDouble(stringCellValue.trim());
            }else {
                doubleCellValue = cell.getNumericCellValue();
                //这里转字符串类别 处理下poi内部获取空的单元格也处理为0的问题
                cell.setCellType(CellType.STRING);
                stringCellValue = cell.getStringCellValue();
                if (stringCellValue == null || stringCellValue.isEmpty() || stringCellValue.trim().isEmpty()){
                    return null;
                }
            }
            if (Double.class.equals(clazz)){
                return ((T)doubleCellValue);
            }else if (Integer.class.equals(clazz)){
                return ((T)(Integer)doubleCellValue.intValue());
            }else if(Long.class.equals(clazz)){
                return ((T)(Long)doubleCellValue.longValue());
            }else if(BigDecimal.class.equals(clazz)){
                return ((T)BigDecimal.valueOf(doubleCellValue).stripTrailingZeros());
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("getCellValueWithType 数字转换失败 " + e.toString());
        }

        return null;

    }

    /**
     * 判断excel的行是否为空
     * @param row
     * @return 行空返回true
     */
    public static boolean judgeRowIsNull(Row row) {
        if (row == null) {
            return true;
        }
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell != null && !"".equals(getCellStringValue(cell))) {
                return false;
            }
        }
        return true;
    }

    /**
     * 判断excel的单元格是否为空
     * @param sheet
     * @param rowIndex
     * @param columnIndex
     * @return
     */
    public static boolean judgeOneCellIsNull(Sheet sheet,int rowIndex,int columnIndex) {
        Row row = sheet.getRow(rowIndex);
        if (row == null){
            return true;
        }
        Cell cell = row.getCell(columnIndex);
        return cell == null;
    }
    /**
     * 把空的单元格初始化并填充基本样式，同时自动调整列宽
     * 自动调整一遍列宽
     * @param sheet
     * @param workbook
     * @param lastRow 末尾行
     * @param lastColumn 末尾列
     */
    public static void initNullCellAndAutoSize(Sheet sheet,Workbook workbook,int lastRow,int lastColumn){
        CellStyle contentStyle = getGeneralContentStyle(workbook);
        for (int columnIndex = 0; columnIndex <= lastColumn; columnIndex++) {
            for (int rowIndex = 0; rowIndex <= lastRow; rowIndex++) {
                if (judgeOneCellIsNull(sheet,rowIndex,columnIndex)){
                    setValue(sheet,rowIndex,columnIndex,"",contentStyle);
                }
            }
            sheet.autoSizeColumn(columnIndex,true);
        }
    }

    /**
     * 设置单元格的值 列宽
     * @param st
     * @param rowIndex
     * @param columnIndex
     * @param value
     * @param style
     * @param accurateWidth 英寸 这里的列宽近似就是Excel右键编辑的实际列宽，会通过计算得到像素
     */
    public static void setValue(Sheet st, int rowIndex, int columnIndex,
                                Object value, CellStyle style,int accurateWidth) {
        if (accurateWidth<0){
            accurateWidth = 10;
        }
        if (accurateWidth > EXCEL_MAX_ACCURATE_WIDTH){
            accurateWidth = 50;
        }
        setValue(st, rowIndex, columnIndex, value, style);
        st.setColumnWidth(columnIndex,256*accurateWidth+184);
    }

    /**
     * 设置单元格的值 基本啥类型都能处理
     * <br /> 日期类型的单元格，需要在调用层手动添加DataFormat
     * <br /> 不应该过多的创建DataFormat，所以没有在这里处理格式问题
     * @param st
     * @param rowIndex
     * @param columnIndex
     * @param value
     * @param style
     */
    public static void setValue(Sheet st, int rowIndex, int columnIndex,
                                Object value, CellStyle style) {
        Row row = st.getRow(rowIndex);
        if (row == null) {
            row = st.createRow(rowIndex);
        }
        Cell cell = row.getCell(columnIndex);
        if (cell == null) {
            cell = row.createCell(columnIndex);
        }
        if (style != null) {
            cell.setCellStyle(style);
        }
        if (value == null){
            cell.setCellValue("");
            return;
        }
        if (value instanceof BigDecimal){
            BigDecimal bigDecimalValue = (BigDecimal) value;
            double doubleValue = bigDecimalValue.doubleValue();
            String numStr = bigDecimalValue.toPlainString();
            if (bigDecimalValue.longValue() > SCIENTIFIC_NUM){
                cell.setCellValue(numStr);
            }else {
                cell.setCellValue(doubleValue);
            }
        }else if (value instanceof Double || value instanceof Long){
            BigDecimal decimal = new BigDecimal(value.toString());
            double doubleValue = decimal.doubleValue();
            String numStr = decimal.toPlainString();
            if (decimal.longValue() > SCIENTIFIC_NUM){
                cell.setCellValue(numStr);
            }else {
                cell.setCellValue(doubleValue);
            }
        }else if (value instanceof Number){
            double doubleValue = ((Number) value).doubleValue();
            String numStr = Double.toString(doubleValue);
            if (doubleValue > SCIENTIFIC_NUM){
                cell.setCellValue(numStr);
            }else {
                cell.setCellValue(doubleValue);
            }
        } else if (value instanceof Date){
            Date dateValue = (Date) value;
            cell.setCellValue(dateValue);
        } else if (value instanceof LocalDate){
            //需要在外层手动设置DataFormat
            LocalDate dateValue = ((LocalDate) value);
            cell.setCellValue(dateValue);
        } else if (value instanceof LocalTime){
            //Excel中 0.xxxx的纯时间 代表的是 1900年1月0日 这样一个不存在的日期 需要手动-1处理
            LocalTime dateValue = ((LocalTime) value);
            LocalDateTime localDateTime = dateValue.atDate(LocalDate.of(1900, 1, 1));
            cell.setCellValue(localDateTime);
            double cellValue = cell.getNumericCellValue();
            cell.setCellValue(cellValue - 1);
        }else if (value instanceof LocalDateTime){
            LocalDateTime dateValue = ((LocalDateTime) value);
            cell.setCellValue(dateValue);
        } else {
            String stringValue = value.toString();
            cell.setCellValue(stringValue);
        }

    }

    /**
     * 组装报错信息
     * @param rowIndex 行下标需要+1
     * @param columnIndex 列行下标需要+1
     * @param exMsg 错误信息
     * @return
     */
    public static String combinePositionMsg(int rowIndex,int columnIndex,String exMsg){
        return "第"+ (rowIndex+1) + "行第" + (columnIndex+1) + "列 "+exMsg;
    }
    /**
     * 组装报错信息 适合业务层特殊处理时使用
     * @param rowIndex 行下标需要+1
     * @param columnTitle 列标题
     * @param exMsg 错误信息
     * @return
     */
    public static String combinePositionMsg(int rowIndex,String columnTitle,String exMsg){
        return "第"+ (rowIndex+1) + "行" + columnTitle + exMsg;
    }

    /**
     * 保存文件
     * @param savePath
     * @param workbook
     */
    public static void saveWorkbook(Path savePath,Workbook workbook){
        if (Files.notExists(savePath)){
            try {
                Files.createDirectories(savePath.getParent());
                Files.createFile(savePath);
            } catch (Exception e) {
                throw new RuntimeException(e.toString());
            }
        }
        try (OutputStream outputStream = Files.newOutputStream(savePath, StandardOpenOption.WRITE,StandardOpenOption.CREATE,StandardOpenOption.TRUNCATE_EXISTING)){
            workbook.write(outputStream);
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
    public static void cloneRow(Sheet sheet,Row originRow,int targetRowIndex) {
        Row newRow = sheet.createRow(targetRowIndex);
        newRow.setHeight(originRow.getHeight());
        for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.getFirstRow() == originRow.getRowNum()) {
                // 如果该合并区域是从复制的原始行开始的，则将其复制到新行，并更新其所属行
                CellRangeAddress newMergedRegion = new CellRangeAddress(
                        targetRowIndex,
                        targetRowIndex + mergedRegion.getLastRow() - mergedRegion.getFirstRow(),
                        mergedRegion.getFirstColumn(),
                        mergedRegion.getLastColumn()
                );
                sheet.addMergedRegion(newMergedRegion);
            }
        }
        for (int i = originRow.getFirstCellNum(); i < originRow.getLastCellNum(); i++) {
            Cell cell = originRow.getCell(i);
            if (cell == null) {
                continue;
            }
            Cell newCell = newRow.createCell(i);
            newCell.setCellStyle(cell.getCellStyle());
            newCell.setCellType(cell.getCellType());
            switch (cell.getCellType()) {
                case STRING:
                    newCell.setCellValue(cell.getStringCellValue());
                    break;
                case NUMERIC:
                    newCell.setCellValue(cell.getNumericCellValue());
                    break;
                case BOOLEAN:
                    newCell.setCellValue(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    newCell.setCellValue(cell.getCellFormula());
                    break;
                case BLANK:
                    newCell.setBlank();
                    break;
                default:
                    break;
            }
        }
    }
}
